package com.src;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mysql.jdbc.StringUtils;
import com.src.model.Experience;
import com.src.model.User;

public class DBConnection {
	
	public static Boolean isInFavorites(String uname, String favUser){
	
		Connection conn = null;
		try {
			conn = DBConnection.createConnection();
			Statement statement = conn.createStatement();
			Statement statement4 = conn.createStatement();
			Statement statement5 = conn.createStatement();
			
			String query4 = "SELECT id FROM User WHERE userName = '"+uname+"'";
			statement4 = conn.prepareStatement(query4);
			ResultSet rs = statement4.executeQuery(query4);
			int userId = 0;
			if(rs.next()) userId = rs.getInt("id");
			
			String query5 = "SELECT id FROM User WHERE userName = '"+favUser+"'";
			statement5 = conn.prepareStatement(query5);
			rs = statement5.executeQuery(query5);
			int favUserId = 0;
			if(rs.next()) favUserId = rs.getInt("id");
			
			String query = "SELECT id FROM FavouriteUser WHERE userID = '"+userId+"' "
					+ "AND favUserID = '"+favUserId+"'" ;
			statement = conn.prepareStatement(query);
			rs = statement.executeQuery(query);
			if(rs.next()) return true;
			else return false;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public static Boolean saveFavorite(String uname, String favUser){
		Connection conn = null;
		PreparedStatement statement2 = null;
		PreparedStatement statement4 = null;
		PreparedStatement statement5 = null;
		Boolean saved = true;
		
		
		int status = 1;

		try {
			conn = DBConnection.createConnection();
			
			String query4 = "SELECT id FROM User WHERE userName = '"+uname+"'";
			statement4 = conn.prepareStatement(query4);
			ResultSet rs = statement4.executeQuery(query4);
			int userId = 0;
			if(rs.next()) userId = rs.getInt("id");
			
			String query5 = "SELECT id FROM User WHERE userName = '"+favUser+"'";
			statement5 = conn.prepareStatement(query5);
			rs = statement5.executeQuery(query5);
			int favUserId = 0;
			if(rs.next()) favUserId = rs.getInt("id");
			
			String query = "INSERT INTO FavouriteUser (userID, favUserID, status)"
					+ "VALUES ('"
					+ userId
					+ "','"
					+ favUserId
					+ "','"
					+ status + "')";
			statement2 = conn.prepareStatement(query);
			Boolean rset = statement2.execute();
			if(saved){
				System.out.println("taha");
			}


		} catch (Exception e) {
			System.out.println("false");
			System.out.println("Database error" + e.toString());
			saved = false;
		} finally {
		
		}

		return saved;

	}
	
	
	
	@SuppressWarnings("finally")
	public static Connection createConnection() throws Exception {
		Connection con = null;
		try {
			Class.forName(Constants.dbClass);
			con = DriverManager.getConnection(Constants.dbUrl, Constants.dbUser, Constants.dbPwd);
		} catch (Exception e) {
			throw e;
		} finally {
			return con;
		}
	}

	public static Boolean updateAvatar(String username, String url) throws Exception {

		Boolean insertStatus;

		Connection dbConn = DBConnection.createConnection();
		Statement stmt = dbConn.createStatement();
		String q = "SELECT id FROM User WHERE userName = '" + username + "'";

		ResultSet r = stmt.executeQuery(q);
		if(r.next()) System.out.println("hasan ferit");
		int uid = r.getInt("id");
		System.out.println("uid: " + uid);
		String query = "INSERT INTO ImageProfile(userID, link) values('"+uid+ "',"+"'" + url + "')";
		int records = stmt.executeUpdate(query);
		//When record is successfully inserted
		if (records > 0) {
			insertStatus = true;
		}
		else{
			insertStatus = false;
		}
		dbConn.close();
		return insertStatus;
	}

	public static boolean checkLogin(String uname, String pwd) throws Exception {
		boolean isUserAvailable = false;
		Connection dbConn = null;
		try {
			try {
				dbConn = DBConnection.createConnection();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Statement stmt = dbConn.createStatement();
			String query = "SELECT * FROM User WHERE userName = '" + uname
					+ "' AND password=" + "'" + pwd + "'";
			ResultSet rs = stmt.executeQuery(query);

			if (rs.next()) {
				isUserAvailable = true;
			} else {
				isUserAvailable = false;
			}
		} catch (SQLException sqle) {
			throw sqle;
		} catch (Exception e) {
			if (dbConn != null) {
				dbConn.close();
			}
			throw e;
		} finally {
			if (dbConn != null) {
				dbConn.close();
			}
		}
		return isUserAvailable;
	}

	public static void insertVote(int helpful, int unhelpful, int spam){



	}

	public static boolean insertUser(String firstName,String lastName,String email, String uname, String pwd) throws SQLException, Exception {
		boolean insertStatus = false;
		Connection dbConn = null;
		try {
			try {
				dbConn = DBConnection.createConnection();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			Statement stmt = dbConn.createStatement();
			String query = "INSERT into User(firstName,lastName,userName,email,password) values('"+firstName+ "',"+"'"
					+ lastName + "','"+ uname + "','" + email + "','"+ pwd + "')";
			//System.out.println(query);
			int records = stmt.executeUpdate(query);
			//When record is successfully inserted
			if (records > 0) {
				insertStatus = true;
			}
		} catch (SQLException sqle) {
			//sqle.printStackTrace();
			throw sqle;
		} catch (Exception e) {
			//e.printStackTrace();
			// TODO Auto-generated catch block
			if (dbConn != null) {
				dbConn.close();
			}
			throw e;
		} finally {
			if (dbConn != null) {
				dbConn.close();
			}
		}
		return insertStatus;
	}

	public static List <Experience> searchExperiences(String tag, String type, String  numberOfResult){
		List<Experience> experienceList = new ArrayList<Experience>();
		Experience experience = null;
		Connection conn;
		ResultSet rs = null;
		Boolean isFailed = false;

		try {
			conn = DBConnection.createConnection();

			if(!StringUtils.isEmptyOrWhitespaceOnly(type)){
				if(type.equals("1")){
					String wildcard = "%" + tag + "%";
					String query = "SELECT e.id, e.body,e.expDate,e.spam,e.unhelpful,e.helpful,GROUP_CONCAT(DISTINCT t.body SEPARATOR '; ') AS tagcloud,u.userName,l.latitude,l.longitude,l.exact "
							+ "FROM Experience e , MapTag mt ,MapExperience me,User u,Tag t,Location l WHERE e.status=1 AND "
							+ "e.id=mt.experienceID AND e.id=me.experienceID AND me.userID=u.id AND mt.experienceID =e.id AND e.locationID=l.id AND t.body LIKE ? GROUP BY e.id ";

					PreparedStatement  statement = conn.prepareStatement(query);
					statement.setString(1,wildcard);
					rs = statement.executeQuery();
				} else if(type.equals("0")){
					String query = "SELECT e.id, e.body,e.expDate,e.spam,e.unhelpful,e.helpful,GROUP_CONCAT( DISTINCT t.body SEPARATOR '; ') AS tagcloud,u.userName,l.latitude,l.longitude,l.exact "
							+ "FROM Experience e , MapTag mt ,MapExperience me,User u,Tag t,Location l WHERE e.status=1 AND "
							+ "e.id=mt.experienceID AND e.id=me.experienceID AND me.userID=u.id AND mt.experienceID =e.id AND e.locationID=l.id  GROUP BY e.id ORDER BY e.expDate DESC LIMIT ?";
					String nofResult = StringUtils.isNullOrEmpty(numberOfResult) ? "5" : numberOfResult; 
					PreparedStatement  statement = conn.prepareStatement(query);
					statement.setInt(1,Integer.valueOf(nofResult));
					rs = statement.executeQuery();
				} else {
					isFailed = true;
				} 
				if(!isFailed){	
					while(rs.next()){
						experience = new Experience();
						experience.setID(rs.getInt("id"));
						experience.setText(rs.getString("body"));
						experience.setCreatedUserName(rs.getString("username"));
						experience.setCreatedDate(rs.getDate("expDate"));
						experience.setTags(rs.getString("tagcloud"));
						experience.setLatitude(rs.getFloat("latitude"));
						experience.setLongitude(rs.getFloat("longitude"));
						experience.setSpam(rs.getInt("spam"));
						experience.setHelpful(rs.getInt("helpful"));
						experience.setUnhelpful(rs.getInt("unhelpful"));
						experience.setExact(rs.getString("exact"));

						experienceList.add(experience);
					}
				} else {
					experienceList = null;
				}
			}
		}catch (Exception e) {
			System.out.println("Database error"  + e.toString());
			experienceList = null;
		}

		return experienceList;
	}

	public static Boolean saveVote(int expID, String type) throws SQLException{
		boolean insertStatus = false;
		Connection dbConn = null;
		System.out.print("utku bilmiyo");
		try {
			try {
				dbConn = DBConnection.createConnection();
			} catch (Exception e) {
				System.out.println("Internal Error" + e.toString());
			}
			Statement stmt = dbConn.createStatement();
			int x = 16;
			String query = "UPDATE Experience SET " + type + "=" + x + " WHERE id=" + expID;
			//String query = null;
			//String query = "INSERT into Experience(e.id,e.text,e.userid,e.date,te.TagBody) values('"+name+ "',"+"'"	+ uname + "','" + pwd + "')";
			//System.out.println(query);
			int records = stmt.executeUpdate(query);
			//System.out.println(records);
			//When record is successfully inserted
			if (records > 0) {
				insertStatus = true;
			}
		} catch (SQLException sqle) {
			//sqle.printStackTrace();
			throw sqle;
		} catch (Exception e) {
			//e.printStackTrace();
			// TODO Auto-generated catch block
			if (dbConn != null) {
				dbConn.close();
			}
			throw e;
		} finally {
			if (dbConn != null) {
				dbConn.close();
			}
		}
		insertStatus = true;
		return insertStatus;
	}
	public static Boolean saveExperience(String tag, String body, String username, 
			float latitude, float longitude, String exact, String imageURL) throws Exception {
		
		boolean insertStatus = true;
		Connection conn = null;
		ResultSet result = null;
		conn = DBConnection.createConnection();
		String query = "INSERT INTO Location (latitude,longitude,exact) VALUES( ?, ?, ? )";
		PreparedStatement  statement = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
		statement.setFloat(1, latitude);
		statement.setFloat(2, longitude);
		statement.setString(3, exact);
		
		statement.executeUpdate();
		result =  statement.getGeneratedKeys();
		
		int locID = 0;
		if (result.next()) {
			locID = result.getInt(1);
		}
	
		query = "INSERT INTO Experience (body, locationID)"
				+ " VALUES( ?, ?)";
		statement = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
		statement.setString(1, body);
		statement.setInt(2, locID);
		
		statement.executeUpdate();
		result =  statement.getGeneratedKeys();
		
		int expID = 0;
		if (result.next()) {
			expID = result.getInt(1);
		}
		
		query = "INSERT INTO ImageExperience (expID, link)"
				+ " VALUES( ?, ?)";
		statement = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
		statement.setInt(1, expID);
		statement.setString(2, imageURL);
		
		statement.executeUpdate();
		result =  statement.getGeneratedKeys();
		
		
		query = "SELECT id FROM User WHERE userName = '" + username + "'";
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);
		
		int userID = 0;
		if(rs.next()) userID = rs.getInt("id");
		
		
		query = "INSERT INTO MapExperience (userID,experienceID) VALUES( ?, ? )";
		statement = conn.prepareStatement(query);
		statement.setInt(1, userID);
		statement.setInt(2, expID);
		statement.executeUpdate();

		String delims = "[;]";
		String[] tokens = tag.split(delims);
		for (int i = 0; i < tokens.length; i++) {
			query = "INSERT INTO Tag (body) VALUES(?)";
			statement = conn.prepareStatement(query,
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, tokens[i]);
			statement.executeUpdate();
			result = statement.getGeneratedKeys();
			Integer tagID = 0;
			if (result.next()) {
				tagID = result.getInt(1);
			}
			if (tagID != null) {
				query = "INSERT INTO MapTag(experienceID,tagID) VALUES( ? , ? )";
				statement = conn.prepareStatement(query);
				statement.setInt(1, expID);
				statement.setInt(2, tagID);
				statement.executeUpdate();
			}
		}
		
		/*
		try {

			String query = "INSERT INTO Experience (body,locationID) VALUES( ?, ? )";
			PreparedStatement  statement = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
			statement.setString(1,body);
			statement.setInt(2, 1);
			statement.executeUpdate();
			result =  statement.getGeneratedKeys();
			if(result.next()){
				id = result.getInt(1);
			}
			if(id != null){
				query = "INSERT INTO MapExperience (userID,experienceID) VALUES( ?, ? )";
				statement = conn.prepareStatement(query);
				statement.setInt(1, userID);
				statement.setInt(2,id);
				statement.executeUpdate();
				String delims = "[;]";
				String[] tokens = tag.split(delims);
				for(int i=0;i<tokens.length;i++){
					query = "INSERT INTO Tag (body) VALUES(?)";
					statement = conn.prepareStatement(query,Statement.RETURN_GENERATED_KEYS);
					statement.setString(1, tokens[i]);
					statement.executeUpdate();
					result = statement.getGeneratedKeys();
					Integer tagID = 0;
					if(result.next()){
						tagID = result.getInt(1);
					}
					if(tagID != null){
						query = "INSERT INTO MapTag(experienceID,tagID) VALUES( ? , ? )";
						statement = conn.prepareStatement(query);
						statement.setInt(1, id);
						statement.setInt(2,tagID);
						statement.executeUpdate();
					}

				}
			}
		} catch(Exception e) {
			throw e;
		}
*/
		return insertStatus;
	}

	public static User getUserInfo(String uname) {
		User user= null;
		Connection conn;
		ResultSet rs = null;
		ResultSet rsi = null;
		Boolean isFailed = false;
		Experience experience = null;
		List<Experience> experienceList = new ArrayList<Experience>();
		try {
			conn = DBConnection.createConnection();

			String query = "SELECT * FROM User WHERE userName = ? ";

			PreparedStatement  statement = conn.prepareStatement(query);
			statement.setString(1,uname);
			rs = statement.executeQuery();
			if(rs.next()){
				user = new User();
				user.setFirstName(rs.getString("firstName"));
				user.setLastName(rs.getString("lastName"));
				user.setEmail(rs.getString("email"));
				int uid = rs.getInt("id");
				query = "SELECT link FROM ImageProfile WHERE userID = ? ";
				statement = conn.prepareStatement(query);
				statement.setInt(1,uid);
				rsi = statement.executeQuery();
				String avatarLink = null;
				if (rsi.next()){
					avatarLink = rsi.getString("link");
				}
				user.setAvatarLink(avatarLink);
				
				
				
				query = "SELECT e.id, e.body,e.expDate,e.spam,e.unhelpful,e.helpful,GROUP_CONCAT(DISTINCT t.body SEPARATOR '; ') AS tagcloud,l.latitude,l.longitude,l.exact "
						+ "FROM Experience e , MapTag mt ,MapExperience me,Tag t,Location l ,User u WHERE e.status=1 AND "
						+ "e.id=mt.experienceID AND e.id=me.experienceID  AND mt.experienceID =e.id AND e.locationID=l.id AND u.id=me.userID AND  u.userName = ? GROUP BY e.id";

				statement = conn.prepareStatement(query);
				statement.setString(1,uname);
				rs = statement.executeQuery();
				int expID = 0;
				while(rs.next()){
					experience = new Experience();
					expID = rs.getInt("id");
					experience.setID(expID);
					
					query = "SELECT link FROM ImageExperience WHERE expID = ? ";
					statement = conn.prepareStatement(query);
					statement.setInt(1,expID);
					ResultSet res = statement.executeQuery();
					String link = null;
					if(res.next()) link = res.getString("link");
					
					experience.setImageURL(link);
					experience.setText(rs.getString("body"));
					experience.setCreatedDate(rs.getDate("expDate"));
					experience.setTags(rs.getString("tagcloud"));
					experience.setLatitude(rs.getFloat("latitude"));
					experience.setLongitude(rs.getFloat("longitude"));
					experience.setSpam(rs.getInt("spam"));
					experience.setHelpful(rs.getInt("helpful"));
					experience.setUnhelpful(rs.getInt("unhelpful"));
					experience.setExact(rs.getString("exact"));

					experienceList.add(experience);
				}
				user.setExperienceList(experienceList);

			}
		} catch (Exception e) {
			user = null;
		}
		return user;
	}

	public static List<String> searchUsers(String tag, String numberOfResults) throws Exception {
		// TODO Auto-generated method stub
		List<String> usernameList = new ArrayList<String>();

		User user= null;
		Connection conn;
		ResultSet rs = null;
		Boolean isFailed = false;

		conn = DBConnection.createConnection();

		String query = "SELECT userName FROM User";
		String nofResult = StringUtils.isNullOrEmpty(numberOfResults) ? "5" : numberOfResults; 

		PreparedStatement  statement = conn.prepareStatement(query);
		rs = statement.executeQuery();

		while(rs.next()){
			usernameList.add(rs.getString("userName"));
		}
		if(usernameList.size() < Integer.parseInt(nofResult)){
			for (int i = 0; i < usernameList.size(); i++) {
				if(!usernameList.get(i).toLowerCase().contains(tag.toLowerCase())){
					usernameList.remove(i);
				}
			}
		}else{
			for (int i = 0; i < Integer.parseInt(nofResult); i++) {
				if(!usernameList.get(i).toLowerCase().contains(tag.toLowerCase())){
					usernameList.remove(i);
				}
			}
		}
		for (int i = 0; i < usernameList.size(); i++) {
			String uname = usernameList.get(i);
			query = "SELECT id FROM User WHERE username =  '" + uname + "'";
			Statement stmt = conn.createStatement();
			ResultSet rset = stmt.executeQuery(query);
			int id = 0;
			if(rset.next()) id = rset.getInt("id");
			query = "SELECT link FROM ImageProfile WHERE userID =  '" + id + "'";
			stmt = conn.createStatement();
			rset = stmt.executeQuery(query);
			String link = null;
			if(rset.next()) link = rset.getString("link");
			
			String temp = usernameList.get(i)+ ":link";
			usernameList.remove(i);
			usernameList.add(i, temp);
		}
		return usernameList;
	}

	public static Boolean removeFavorite(String uname, String favUser) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement statement2 = null;
		PreparedStatement statement4 = null;
		PreparedStatement statement5 = null;
		Boolean saved = true;
		
		
		int status = 1;

		try {
			conn = DBConnection.createConnection();
			
			String query4 = "SELECT id FROM User WHERE userName = '"+uname+"'";
			statement4 = conn.prepareStatement(query4);
			ResultSet rs = statement4.executeQuery(query4);
			int userId = 0;
			if(rs.next()) userId = rs.getInt("id");
			
			String query5 = "SELECT id FROM User WHERE userName = '"+favUser+"'";
			statement5 = conn.prepareStatement(query5);
			rs = statement5.executeQuery(query5);
			int favUserId = 0;
			if(rs.next()) favUserId = rs.getInt("id");
			
			String query = "DELETE FROM FavouriteUser WHERE userID = '"+userId+"'"
					+ " AND favUserID = '"+favUserId+"'";
			statement2 = conn.prepareStatement(query);
			Boolean rset = statement2.execute();
			if(rset) System.out.println("hosa");

		} catch (Exception e) {
			System.out.println("false");
			System.out.println("Database error" + e.toString());
			saved = false;
		} finally {
		
		}

		return saved;

	}
}

